package DBLayer;
import ModelLayer.Customer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.Sale;
import ModelLayer.SalesLine;


public class DBSale implements IFDBSale 
{
    private Connection con;
    
    public DBSale() 
    {
       con = DBConnection.getInstance().getDBcon();
    }
    
    @Override
    public ArrayList<Sale> Sales(int saleID)
    {
        return miscWhere("sId = " + saleID, true);
    }
   
    @Override
    public Sale findSale(int saleID, boolean retriveAssociation)
    {
        Sale saleObj = new Sale();
        saleObj = singleWhere("sId = " + saleID, retriveAssociation);
        return saleObj;
    }
    
    @Override
    public Sale findLatestSale(boolean retriveAssociation)
    {
        Sale saleObj = new Sale();
        
        saleObj = miscWhere("", false).get(0);
        
        return saleObj;
    }
    
    @Override
    public ArrayList<Sale> getAllSales(boolean retriveAssociation) 
    {
        ArrayList list = new ArrayList();
        list = miscWhere("", retriveAssociation);
        return list;
    }
       
    @Override
    public Sale searchSaleID(int saleID, boolean retriveAssociation) 
    {
        String wClause = "ID like '%" + saleID + "%'";
        
        return singleWhere(wClause, retriveAssociation);
    }    
    
    public int MaxID()
    {
        String query = "SELECT max(sId) FROM Sale";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading max id: " + e);
        } 
        return id;     
    }
    
    
    private Sale singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Sale saleObj = new Sale();
        String query =  buildQuery(wClause);
        System.out.println(query);
        try
        {
	   Statement stmt = con.createStatement();
	   stmt.setQueryTimeout(5);
	   results = stmt.executeQuery(query);
           
	   if( results.next())
           {
	       saleObj = buildOrder(results);
           }
           
	   stmt.close();
	}
	catch(Exception e)
        {
	   
        }
        
	return saleObj;
    }
    
    private ArrayList<Sale> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Sale> list = new ArrayList<Sale>();

        String query =  buildQuery(wClause);
        System.out.println(query);
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while( results.next())
            {
                Sale saleObj = new Sale();
                saleObj = buildOrder(results);	
                list.add(saleObj);
            }

            stmt.close();
            if(retrieveAssociation)
            {
                for(Sale s : list)
                {
                    IFDBCustomer dbCust = new DBCustomer();
                    Customer cObj = dbCust.findCustomer(s.getCustomer().getId(), false);
                    System.out.println("Customer id is selected");
                    s.setCustomer(cObj);
                }
            }
        }	
        catch(Exception e)
        {

        }
        
        return list;
    }
    
    private Sale buildOrder(ResultSet results)
    {
        Sale saleObj = new Sale();
        
        try
        {
           saleObj.setSaleID(results.getInt(1));
           saleObj.setCustomer(new Customer(results.getInt(2)));
           saleObj.setStatus(results.getBoolean(3));
           saleObj.setDate(results.getString(4));
           saleObj.setTotalPrice(results.getDouble(5));
        }
        catch(Exception e)
        {

        }

        return saleObj;
    }
 
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Sale";

            if (wClause.length()>0)
            {
                query = query+" WHERE "+ wClause;
            }

            return query;
    }

    @Override
    public void insertSale(Sale sale)
    {  
        String query = "INSERT INTO Sale (cId, date, status, totalPrice) VALUES ('"+
                    sale.getCustomer().getId()  + "','"  +
                    sale.getDate()  + "','"  +
                    sale.getStatus() + "','"  +
                    sale.getTotalPrice()  + 
                   "')";
        
        
         System.out.println("Update query: " + query);
      
         try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            
            
            stmt.close();
        }
        catch(Exception e)
        {
            
        }
    }


    
} 